DROP TABLE IF EXISTS `dwd_dim_sku_info`;
CREATE EXTERNAL TABLE `dwd_dim_sku_info` (
`id` string COMMENT '商品 id',
`spu_id` string COMMENT 'spuid',
`price` double COMMENT '商品价格',
`sku_name` string COMMENT '商品名称',
`sku_desc` string COMMENT '商品描述',
`weight` double COMMENT '重量',
`tm_id` string COMMENT '品牌 id',
`tm_name` string COMMENT '品牌名称',
`category3_id` string COMMENT '三级分类 id',
`category2_id` string COMMENT '二级分类 id',
`category1_id` string COMMENT '一级分类 id',
`category3_name` string COMMENT '三级分类名称',
`category2_name` string COMMENT '二级分类名称',
`category1_name` string COMMENT '一级分类名称',
`spu_name` string COMMENT 'spu 名称',
`create_time` string COMMENT '创建时间'
)
COMMENT '商品维度表'
PARTITIONED BY (`dt` string)
stored as parquet
location '/warehouse/gmall/dwd/dwd_dim_sku_info/'
tblproperties ("parquet.compression"="lzo");

insert overwrite table dwd_dim_sku_info partition(dt='2020-08-03')
select
    sku.id,
    sku.spu_id,
    sku.price,
    sku.sku_name,
    sku.sku_desc,
    sku.weight,
    sku.tm_id,
    ob.tm_name,
    sku.category3_id,
    c2.id category2_id,
    c1.id category1_id,
    c3.name category3_name,
    c2.name category2_name,
    c1.name category1_name,
    spu.spu_name,
    sku.create_time
from
(
select
    a
    ,b
    ,c
from ods_sku_info
where dt='2020-08-03'
) t1
join
(
select
    d
    ,e
    ,f
from ods_base_trademark
where dt='2020-08-03'
) t2 on t1.a=t2.d
LEFT join
(
select
    gg
    ,hh
    ,jj
from ods_spu_info
where dt='2020-08-03'
)t3 on t1.id = t3.spu_id
FULL join
(
select
    abc
    ,def
    ,gsa
from aa.ods_base_category3
where dt='2020-08-03'
)t4
on t1.category3_id=t4.id
LEFT join
(
select
    abcc
    ,defc
    ,gsac
from a.ods_base_category4
where dt='2020-08-03'
)t5
on t4.category2_id=t5.id
join
(
select
    abceer
    ,defasd
    ,gsaqwe
from db.ods_base_category7
where dt='2020-08-03'
)t6 on t5.category1_id=t6.id;